package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.compiere.model.MAccount;
import org.compiere.model.MAsset;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import org.utils.AnalogConvertor_RU;

import extend.org.compiere.utils.Util;

public class A_Asset_Form_Inv8 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	private Integer p_M_Locator_ID, p_chiefComm, p_memberComm1, p_memberComm2,  p_memberComm3, p_memberComm4, p_memberComm5, p_memberChecking;
	
	private int  mAD_Org_ID;
	private Workbook wb;
	private Sheet sheet;
	Timestamp p_DateDoc, p_DateDocBase;
	
	String ResultPath, p_DocumentBaseNo, p_DocumentNo;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	mAD_Org_ID = Env.getAD_Org_ID(m_ctx);
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("DocumentNo"))
			p_DocumentNo = (String) para[i].getParameter();
		else if (name.equals("DateDoc"))
			p_DateDoc = (Timestamp) para[i].getParameter();
		else if (name.equals("M_Locator_ID"))
			p_M_Locator_ID = para[i].getParameterAsInt();
		else if (name.equals("DocumentBaseNo"))
			p_DocumentBaseNo = (String) para[i].getParameter();
		else if (name.equals("DateDocBase"))
			p_DateDocBase = (Timestamp) para[i].getParameter();
		else if (name.equals("chiefComm"))
			p_chiefComm = para[i].getParameterAsInt();
		else if (name.equals("memberComm1"))
			p_memberComm1 = para[i].getParameterAsInt();
		else if (name.equals("memberComm2"))
			p_memberComm2 = para[i].getParameterAsInt();
		else if (name.equals("memberComm3"))
			p_memberComm3 = para[i].getParameterAsInt();
		else if (name.equals("memberComm4"))
			p_memberComm4 = para[i].getParameterAsInt();
		else if (name.equals("memberComm5"))
			p_memberComm5 = para[i].getParameterAsInt();
		else if (name.equals("memberChecking"))
			p_memberChecking = para[i].getParameterAsInt();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		//File path

		ResultPath = "Form_Inv8_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 11) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		wb = new XSSFWorkbook(entry.getInputStream());
		sheet = wb.getSheetAt(0);
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(4).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(11).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		sheet.getRow(12).getCell(9).setCellValue(p_DocumentNo);
		sheet.getRow(12).getCell(11).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_DateDoc)));
		
		Timestamp currdate = new Timestamp(System.currentTimeMillis());
//		String sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_mol;
//		int id = DB.getSQLValue(get_TrxName(), sqlheader);
//		sheet.getRow(25).getCell(3).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
//		sheet.getRow(25).getCell(9).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
		sheet.getRow(25).getCell(9).setCellValue((new MLocator(m_ctx, p_M_Locator_ID, get_TrxName()).getValue()));
//		sheet.getRow(71).getCell(4).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
//		sheet.getRow(71).getCell(9).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
		sheet.getRow(71).getCell(9).setCellValue((new MLocator(m_ctx, p_M_Locator_ID, get_TrxName()).getValue()));
		sheet.getRow(73).getCell(1).setCellValue((new SimpleDateFormat("dd").format(currdate)) 
				+ " " + (new SimpleDateFormat("MMMMM").format(currdate))
				+ " " + (new SimpleDateFormat("yyyy").format(currdate)));
		
		String sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberChecking;
		int id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(74).getCell(3).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(74).getCell(9).setCellValue((new MBPartner(m_ctx, p_memberChecking, get_TrxName()).getName()));
		
		//Chairman 
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_chiefComm;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(55).getCell(2).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(55).getCell(8).setCellValue((new MBPartner(m_ctx, p_chiefComm, get_TrxName()).getName()));

		//Commissions
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm1;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(57).getCell(2).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(57).getCell(8).setCellValue((new MBPartner(m_ctx, p_memberComm1, get_TrxName()).getName()));
		
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm2;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(59).getCell(2).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(59).getCell(8).setCellValue((new MBPartner(m_ctx, p_memberComm2, get_TrxName()).getName()));
		
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm3;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(61).getCell(2).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(61).getCell(8).setCellValue(p_memberComm3 == null ? "" : (new MBPartner(m_ctx, p_memberComm3, get_TrxName()).getName()));
		
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm4;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(63).getCell(2).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(63).getCell(8).setCellValue(p_memberComm4 == null ? "" : (new MBPartner(m_ctx, p_memberComm4, get_TrxName()).getName()));
		
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm5;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(65).getCell(2).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(65).getCell(8).setCellValue(p_memberComm5 == null ? "" : (new MBPartner(m_ctx, p_memberComm5, get_TrxName()).getName()));
		
		sheet.getRow(30).getCell(3).setCellValue("№ " + p_DocumentBaseNo + " "+ new SimpleDateFormat("dd-MM-YYYY").format(p_DateDoc));
		
		sheet.getRow(31).getCell(6).setCellValue((new SimpleDateFormat("dd-MM-YYYY").format(currdate)));
		
		//nacalo inventory
		sheet.getRow(33).getCell(2).setCellValue((new SimpleDateFormat("dd-MM-YYYY").format(currdate)));
		//konets inventory
		sheet.getRow(33).getCell(8).setCellValue((new SimpleDateFormat("dd-MM-YYYY").format(currdate)));
		
		
		//Positions
		int row = 46;
		int no = 0, totalQty = 0;
		double totalAmt = 0;
		ArrayList<Integer> assetsid = new ArrayList<Integer>();

//		String sql="SELECT A_Asset_ID FROM A_Asset WHERE AD_Org_ID = "+mAD_Org_ID
//				+" AND C_BPartnerSR_ID = "+p_mol
//			    +" AND IsActive = 'Y'";
		String sql="SELECT A_Asset_ID FROM A_Asset WHERE AD_Org_ID = "+mAD_Org_ID
				+" AND M_Locator_ID = " + p_M_Locator_ID
			    +" AND IsActive = 'Y'";
		PreparedStatement pstmt = null;
		pstmt = DB.prepareStatement (sql.toString(),get_TrxName());
		ResultSet rs = null;
		try {				
		  rs = pstmt.executeQuery();
		  while (rs.next()){
			  assetsid.add(rs.getInt("A_Asset_ID"));
		  }
		} catch (Exception e) {
			  System.out.println("A_Depreciation_Exp. "+e);
		} finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		for (int i=0; i<assetsid.size();i++) {
	        sheet.shiftRows(47, sheet.getLastRowNum(), 1);
			sheet.createRow(47); 
			copyRow(46,47);
		}
			
		for (int i=0; i<assetsid.size();i++) {
			MAsset asset = new MAsset(m_ctx, assetsid.get(i), get_TrxName());
			
			sheet.getRow(row).getCell(0).setCellValue(++no);
			sheet.getRow(row).getCell(1).setCellValue(asset.getName());
			
			MAccount acct = MAccount.get(getCtx(), asset.getAcctValue("A_Asset_Acct"));
			sheet.getRow(row).getCell(2).setCellValue(acct.getAccount().getValue());
			sheet.getRow(row).getCell(3).setCellValue(asset.getAssetServiceDate() == null ? "" : (new SimpleDateFormat("dd.MM.yyyy").format(asset.getAssetServiceDate())));
			sheet.getRow(row).getCell(4).setCellValue(asset.getA_Asset_CreateDate() == null ? "" : (new SimpleDateFormat("dd.MM.yyyy").format(asset.getA_Asset_CreateDate())));
			sheet.getRow(row).getCell(5).setCellValue(asset.getValue());
			sheet.getRow(row).getCell(6).setCellValue(asset.getSerNo());

			sheet.getRow(row).getCell(10).setCellValue(1);
			totalQty += 1;
			BigDecimal bBalCost = asset.getBalanceCost(p_DateDoc);
			if (bBalCost != null) {
				sheet.getRow(row).getCell(11).setCellValue(bBalCost.doubleValue());
				totalAmt += bBalCost.doubleValue();
			} else {
				sheet.getRow(row).getCell(11).setCellValue(0);
			}
			row++;
		}

		sheet.getRow(row).getCell(8).setCellFormula("SUM(I47:I" + row + ")");
		sheet.getRow(row).getCell(9).setCellFormula("SUM(J47:J" + row + ")");
		sheet.getRow(row).getCell(10).setCellFormula("SUM(K47:K" + row + ")");
		sheet.getRow(row).getCell(11).setCellFormula("SUM(L47:L" + row + ")");
		
		sheet.getRow(row + 3).getCell(5).setCellValue(AnalogConvertor_RU.toString(no));
		sheet.getRow(row + 5).getCell(5).setCellValue(AnalogConvertor_RU.toString(totalQty));
		sheet.getRow(row + 7).getCell(5).setCellValue(AnalogConvertor_RU.toString(totalAmt));
		
		sheet.getRow(row + 10).getCell(9).setCellValue(no);
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		return Msg.translate(m_ctx, "Success");
	}	

	private void copyRow(int sourceRowNum, int destinationRowNum)
	   {
        XSSFRow newRow = (XSSFRow) sheet.getRow(destinationRowNum);
        XSSFRow sourceRow = (XSSFRow) sheet.getRow(sourceRowNum);

        // Loop through source columns to add to new row
	       for (int i = 0; i < sourceRow.getLastCellNum(); i++)
	       {
	           // Grab a copy of the old/new cell
	           Cell oldCell = sourceRow.getCell(i);
	           Cell newCell;
	           // If the old cell is null jump to next cell
	           if (oldCell == null)
	           {
	               newCell = null;
	               continue;
	           }
	 
	           newCell = newRow.createCell(i,oldCell.getCellType());

	           // Copy style from old cell and apply to new cell
	           CellStyle newCellStyle = wb.createCellStyle();
	           newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
	           
	           newCell.setCellStyle(newCellStyle);
	 
	           // If there is a cell comment, copy
	           if (newCell.getCellComment() != null)
	           {
	               newCell.setCellComment(oldCell.getCellComment());
	           }
	 
	           // If there is a cell hyperlink, copy
	           if (oldCell.getHyperlink() != null)
	           {
	               newCell.setHyperlink(oldCell.getHyperlink());
	           }
	 
	           // Set the cell data type
	           //newCell.setCellType(oldCell.getCellType());
	 
	           // Set the cell data value
	           /*switch (oldCell.getCellType())
	           {
	               case Cell.CELL_TYPE_BLANK:
	                   newCell.setCellValue(oldCell.getStringCellValue());
	                   break;
	               case Cell.CELL_TYPE_BOOLEAN:
	                   newCell.setCellValue(oldCell.getBooleanCellValue());
	                   break;
	               case Cell.CELL_TYPE_ERROR:
	                   newCell.setCellErrorValue(oldCell.getErrorCellValue());
	                   break;
	               case Cell.CELL_TYPE_FORMULA:
	                   newCell.setCellFormula(oldCell.getCellFormula());
	                   break;
	               case Cell.CELL_TYPE_NUMERIC:
	                   newCell.setCellValue(oldCell.getNumericCellValue());
	                   break;
	               case Cell.CELL_TYPE_STRING:
	                   newCell.setCellValue(oldCell.getRichStringCellValue());
	                   break;
	           }*/
	       }
	   }	
}
